﻿UPDATE so_kach_analiz_kons 
SET K_KONS = 11, N_KONS_OTCH = 'Методическая работа'
WHERE K_SO_KACH_ANALIZ_KONS = 7

GO
print 'ALTER PROCEDURE dbo.RepKachAnaliz'
ALTER PROCEDURE dbo.RepKachAnaliz 
	(
		@D_S datetime,	
		@D_PO datetime,
		@ERROR_MSG varchar(1024) = '' OUTPUT 	-- строка с текстом ошибки. Если ошибка отсуствовала, то пустая
	)
AS
-- Отчета "Карточка качественного анализа обращений"
BEGIN

BEGIN TRY
	BEGIN TRAN
	
	DECLARE @K_KACH_ANALIZ int
	
	-- Формируем основную запись
	--
	INSERT INTO bo_kach_analiz (D_NACH, D_OKON) VALUES (@D_S, @D_PO)

	SELECT @K_KACH_ANALIZ = SCOPE_IDENTITY();

	-- Виды обращений клиентов в центр C_RAZD = 1 чать 1
	-- 	
	INSERT INTO bo_kach_analiz_ (K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, C_SORT )
	SELECT @K_KACH_ANALIZ, 1 AS C_RAZD, 'Виды обращений клиентов в центр' AS N_GRUP, so_kach_analiz_obrasch.N_OBRASCH_OTCH, sum(isnull(C_COUNT,0)) , C_SORT 
	FROM
		so_kach_analiz_obrasch
	LEFT JOIN
	(	
		select Count(K_ZHURNAL_OBRASCH) AS C_COUNT, K_OBRASCH
		FROM b_zhurnal_obrasch 
		WHERE D_OBRASCH between  @D_S and @D_PO
		GROUP BY K_OBRASCH
	) as t 
		ON t.K_OBRASCH = so_kach_analiz_obrasch.K_OBRASCH
	GROUP BY N_OBRASCH_OTCH, C_SORT
	
	-- Виды обращений клиентов в центр чать 2 (кризис по круглосуточной диспетчерской линии)
	-- 	
	INSERT INTO bo_kach_analiz_ (K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, C_SORT )
	SELECT @K_KACH_ANALIZ, 1 AS C_RAZD, 'Виды обращений клиентов в центр' AS N_GRUP, '     кризис по круглосуточной диспетчерской линии' AS N_PUNKT,
	sum(cast(isnull(F_KRIZIS,0) as int)) AS C_COUNT, 4 AS C_SORT
	FROM b_kpo
	WHERE D_OBRASCH between  @D_S and @D_PO
	
	-- Виды обращений клиентов в центр чать 3 (Всего обращений)
	-- 	
	INSERT INTO bo_kach_analiz_ (K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, C_SORT )
	SELECT @K_KACH_ANALIZ, 1 AS C_RAZD, 'Виды обращений клиентов в центр' AS N_GRUP, 'Всего обращений' AS N_PUNKT, 
	Count(K_ZHURNAL_OBRASCH) AS C_COUNT, 1 AS C_SORT
	FROM b_zhurnal_obrasch
	WHERE D_OBRASCH between  @D_S and @D_PO


	-- Причины обращения C_RAZD = 2 (Распредление по причинам обращения на КПО)
	-- 	
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 2 AS C_RAZD, 'Причины обращения' AS N_GRUP, s_prich.N_PRICH, Count(b_kpo.K_KPO) AS C_COUNT
	FROM (sv_kpo_prich RIGHT JOIN s_prich ON sv_kpo_prich.K_PRICH = s_prich.K_PRICH) 
		LEFT JOIN b_kpo ON sv_kpo_prich.K_KPO = b_kpo.K_KPO
	WHERE D_OBRASCH between  @D_S and @D_PO
	GROUP BY s_prich.N_PRICH
	ORDER BY s_prich.N_PRICH


	-- Запрос клиентов в процессе обращения C_RAZD = 3 часть 1 - По виду помощи общее число обращений
	--	Распеределение по запросу на помощь на КПО
	-- 	
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_POD_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 3 AS C_RAZD, 'Запрос клиентов в процессе обращений' AS N_GRUP, 'По виду помощи общее число обращений' AS N_POD_GRUP, 
		N_POM, Count(b_kpo.K_KPO) AS C_COUNT
	FROM b_kpo INNER JOIN sv_kpo_pom ON sv_kpo_pom.K_KPO = b_kpo.K_KPO 
		RIGHT JOIN s_pom ON sv_kpo_pom.K_POM = s_pom.K_POM 
	WHERE b_kpo.D_OBRASCH between  @D_S and @D_PO
	GROUP BY N_POM
	ORDER BY N_POM
	
	-- Запрос клиентов в процессе обращения C_RAZD = 3 часть 2 - По характиристики запроса
	--	Расеределение по характиру запроса на консультации УКС (подробнее)
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_POD_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 3 AS C_RAZD, 'Запрос клиентов в процессе обращений' AS N_GRUP, 'По характеристике запроса' AS N_POD_GRUP, 
		N_HAR_ZAPROS, Count(b_uks_kons.K_UKS_KONS) AS C_COUNT
	FROM b_uks_kons INNER JOIN sv_uks_kons_har_zapros ON sv_uks_kons_har_zapros.K_UKS_KONS = b_uks_kons.K_UKS_KONS
		RIGHT JOIN s_har_zapros ON sv_uks_kons_har_zapros.K_HAR_ZAPROS = s_har_zapros.K_HAR_ZAPROS 
	WHERE b_uks_kons.D_KONS between  @D_S and @D_PO
	GROUP BY N_HAR_ZAPROS
	ORDER BY N_HAR_ZAPROS


	-- Помощь оказываемая клинтам специалистами центра C_RAZD = 4
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, N_PRIM, C_SORT )	select @K_KACH_ANALIZ, 4 AS C_RAZD, 'Помощь, оказываемая клиентам специалистами центра' AS N_GRUP, N_KONS_OTCH,
		isnull(C_COUNT_KPO,0) + isnull(C_COUNT_UKS,0) as C_COUNT, 
		cast(isnull(C_COUNT_KPO,0) as varchar(10)) + ' + ' +  cast(isnull(C_COUNT_UKS,0) as varchar(10)) as N_PRIM, C_SORT
	from
	(
		-- 1. На основании консультаций УКС. Без групповой работы (K_KONS <> 5)
		SELECT K_KONS, Count(K_UKS_KONS) AS C_COUNT_UKS
		FROM b_uks_kons
		where K_KONS <> 5 and b_uks_kons.D_KONS between  @D_S and @D_PO
		GROUP BY K_KONS
	) as t_count_uks
		FULL JOIN
	(
		-- 2. На основании КПО. Без групповой работы (K_KONS <> 5)
		SELECT K_KONS, Count(K_KPO) AS C_COUNT_KPO
		FROM b_kpo
		WHERE F_KONS = 1 and K_KONS <> 5 and b_kpo.D_OBRASCH between  @D_S and @D_PO
		GROUP BY K_KONS
	) as t_coutn_kpo
		ON t_count_uks.K_KONS = t_coutn_kpo.K_KONS
	RIGHT JOIN
		so_kach_analiz_kons 
		ON so_kach_analiz_kons.K_KONS = t_count_uks.K_KONS OR so_kach_analiz_kons.K_KONS = t_coutn_kpo.K_KONS;


	-- Диадные причины C_RAZD = 5
	--	Распределение по Сферам и Конфликтным взаимоотношениям на консультации УКС исключая юр сферу s_prob_tip.K_PROB_TIP <> 6 
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_POD_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 5 AS C_RAZD, 'Диадные причины' AS N_GRUP, s_prob_tip.N_PROB_TIP, s_prob.N_PROB, Count(b_uks_kons.K_UKS_KONS) AS C_COUNT
	FROM 
		b_uks_kons INNER JOIN sv_uks_prob ON b_uks_kons.K_UKS_KONS = sv_uks_prob.K_UKS_KONS
			LEFT JOIN s_prob ON sv_uks_prob.K_PROB = s_prob.K_PROB
				LEFT JOIN s_prob_tip ON s_prob.K_PROB_TIP = s_prob_tip.K_PROB_TIP
	WHERE s_prob_tip.K_PROB_TIP <> 6 and b_uks_kons.D_KONS between  @D_S and @D_PO
	GROUP BY s_prob_tip.N_PROB_TIP, s_prob.N_PROB
	ORDER BY s_prob_tip.N_PROB_TIP, s_prob.N_PROB



	-- Особенности юридического консультирования C_RAZD = 6
	--	Распределение по Конфликтным взаимоотношениям на консультации УКС где сфера юридическая s_prob.K_PROB_TIP = 6  
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 6 AS C_RAZD, 'Особенности юридического консультирования' AS N_GRUP, s_prob.N_PROB, 
		Count(b_uks_kons.K_UKS_KONS) AS C_COUNT
	FROM 
		b_uks_kons INNER JOIN sv_uks_prob ON sv_uks_prob.K_UKS_KONS = b_uks_kons.K_UKS_KONS
			RIGHT JOIN s_prob ON sv_uks_prob.K_PROB = s_prob.K_PROB 
	WHERE s_prob.K_PROB_TIP = 6 and b_uks_kons.D_KONS between  @D_S and @D_PO
	GROUP BY s_prob.N_PROB
	ORDER BY s_prob.N_PROB



	-- Социальный статус клиентов C_RAZD = 7
	--	 Распределение по социальному статусу на КПО  
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 7 AS C_RAZD, 'Социальный статус клиентов' AS N_GRUP, s_socstat.N_SOCSTAT, Count(b_kpo.K_KPO) AS C_COUNT
	FROM b_kpo LEFT JOIN s_socstat ON s_socstat.K_SOCSTAT = b_kpo.K_SOCSTAT
	where  b_kpo.D_OBRASCH between  @D_S and @D_PO
	GROUP BY N_SOCSTAT
	ORDER BY N_SOCSTAT


	-- Категории клиентов (семьи) C_RAZD = 8
	--	 Распределение категориям семьи на УКС  
	--
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT )
	SELECT @K_KACH_ANALIZ, 8 AS C_RAZD, 'Категории клиентов (семьи)' AS N_GRUP, s_kat_sem.N_KAT_SEM, Count(b_uks.K_UKS) AS C_COUNT
	FROM 
		b_uks INNER JOIN sv_uks_kat_sem ON sv_uks_kat_sem.K_UKS = b_uks.K_UKS 
			LEFT JOIN s_kat_sem  ON s_kat_sem.K_KAT_SEM = sv_uks_kat_sem.K_KAT_SEM
	WHERE s_kat_sem.NOM_KAT_SEM <> 0 AND sv_uks_kat_sem.F_OTCHET = 1 AND b_uks.D_DZ between  @D_S and @D_PO
	GROUP BY s_kat_sem.N_KAT_SEM
	ORDER BY s_kat_sem.N_KAT_SEM


	-- Рассчет кол-ва детей до 14 и 18 лет и кол-во семей C_RAZD = 8
	--	Прим: отнисится от разделу 8 но мне неизветно почему
	--
	Declare @DETI_14 int, @DETI_18 int, @SEM_14 int, @SEM_18 int
	
	select @DETI_14 = sum(DETI_14), @DETI_18 = sum(DETI_18), 
		@SEM_14 = sum(case when isnull(DETI_14,0) > 0 then 1 end),
		@SEM_18 = sum(case when isnull(DETI_18,0) > 0 then 1 end)
	from
	(
		SELECT b_sostav_semyi_uks.K_UKS,
			sum(case when b_sostav_semyi_uks.GOD_R >= (Year(@D_PO) - 14) THEN 1 END) as DETI_14,
			sum(case when b_sostav_semyi_uks.GOD_R >= (Year(@D_PO) - 18) THEN 1 END) as DETI_18
		FROM 
		b_sostav_semyi_uks INNER JOIN b_uks ON b_sostav_semyi_uks.K_UKS = b_uks.K_UKS
		WHERE 
			b_sostav_semyi_uks.K_RODSTV_OTN Between 8 And 13 and b_uks.D_DZ between  @D_S and @D_PO

		GROUP BY b_sostav_semyi_uks.K_UKS
	) as t
	
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, N_PRIM )
		VALUES(@K_KACH_ANALIZ, 8, 'Категории клиентов (семьи)', 
		'Kоличесво детей до 14 лет в семьях состоящих на учете', @DETI_14, 'в' + cast(@SEM_14 as varchar(10)) + 'семьях')
	
	INSERT INTO bo_kach_analiz_ ( K_KACH_ANALIZ, C_RAZD, N_GRUP, N_PUNKT, C_COUNT, N_PRIM )
	VALUES(@K_KACH_ANALIZ, 8, 'Категории клиентов (семьи)', 
	'Kоличесво детей до 18 лет в семьях состоящих на учете', @DETI_18, 'в' + cast(@SEM_18 as varchar(10)) + 'семьях')


END TRY
BEGIN CATCH
	
	set @ERROR_MSG = isnull(dbo.GetLastError(),'')
	ROLLBACK TRANSACTION
	RETURN
	
END CATCH;

	COMMIT TRAN
	RETURN
END

